﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.SqlClient;
using System.Text;
using IDAL;
using Models;

namespace DAL
{

    public class UpdateParamInfoDAL:IUpdateParamInfo
    {

        public const string SQL_GET_UPDATEPARAMINFO = @"select top 1 * from UpdateParamInfo";
        public const string SQL_DELETE_ALLINFO = @"delete from UpdateParamInfo;";

        public DataBase DB = new DataBase();

        /// <summary>
        /// 获取参数表中的参数对象
        /// </summary>
        /// <returns></returns>
        public UpdateParamInfo GetUpdateParamInfo()
        {
            UpdateParamInfo updateparaminfo = new UpdateParamInfo();
            DB.DatabaseConnection();
            SqlDataReader reader = DB.GetdataRecords(SQL_GET_UPDATEPARAMINFO);
            try
            {
                if (reader.Read())
                {
                    ReadtoUpdateParamInfo(reader, updateparaminfo);
                }
                else
                {
                    updateparaminfo.NewsSpanTime = 7;
                    updateparaminfo.NewsUpdateTime = System.DateTime.Now;
                    updateparaminfo.NewsTop = 5;
                    updateparaminfo.NewsUpdateSpanTime = 3;
                    updateparaminfo.WorksSpanTime = 7;
                    updateparaminfo.WokrsUpdateTime = System.DateTime.Now;
                    updateparaminfo.WorksUpdateSpanTime = 3;
                    updateparaminfo.WorksTop = 5;
                }
            }
            catch
            {
                updateparaminfo.NewsSpanTime = 7;
                updateparaminfo.NewsUpdateTime = System.DateTime.Now;
                updateparaminfo.NewsTop = 5;
                updateparaminfo.NewsUpdateSpanTime = 3;
                updateparaminfo.WorksSpanTime = 7;
                updateparaminfo.WokrsUpdateTime = System.DateTime.Now;
                updateparaminfo.WorksUpdateSpanTime = 3;
                updateparaminfo.WorksTop = 5;
            }
            DB.CloseDB();
            return updateparaminfo;
        }
        /// <summary>
        /// 更新参数表数据
        /// </summary>
        /// <param name="updateparainfo"></param>
        public int UpdateTheParamInfo(UpdateParamInfo updateparainfo)
        {
            int isSucess = -1;
            DB.DatabaseConnection();
            //清空参数表中的所有信息
            isSucess = DB.UpdateRecords(SQL_DELETE_ALLINFO);
            string InsertSQL = @" INSERT INTO UpdateParamInfo([NewsSpanTime],[NewsUpdateTime],[NewsUpdateSpanTime],[NewsTop],[WorksSpanTime],[WokrsUpdateTime],[WorksUpdateSpanTime],[WorksTop]) 
                                    VALUES( " + updateparainfo.NewsSpanTime + ",'"
                                             + updateparainfo.NewsUpdateTime + "',"
                                             + updateparainfo.NewsUpdateSpanTime + ","
                                             + updateparainfo.NewsTop + ","
                                             + updateparainfo.WorksSpanTime + ",'"
                                             + updateparainfo.WokrsUpdateTime + "',"
                                             + updateparainfo.WorksUpdateSpanTime +","
                                             +updateparainfo.WorksTop + ")";
            isSucess = DB.UpdateRecords(InsertSQL);
            return isSucess;
        }

        /// <summary>
        /// 去掉新闻信息中过时的最新标示
        /// </summary>
        /// <param name="spantime">过时的时间间隔（天数）</param>
        public void UpdateNewsInfoNewFlag(int spantime)
        {
            DB.DatabaseConnection();
            string SQL_UpdateNewsNewFlag = @"update NewsInfo set NewFlag = 0 where DATEDIFF(day,PublishTime,getdate())>"+spantime;
            int isSucess = DB.UpdateRecords(SQL_UpdateNewsNewFlag);
        }

        /// <summary>
        /// 去掉过时的热门标示，并添加新的热门标示
        /// </summary>
        /// <param name="clickcount">点击次数</param>
        public void UpdateNewsInfoHotFlag(int topcount)
        {
            DB.DatabaseConnection();
            string SQL_UpdateNewsHotFlag1 = @"update NewsInfo set HotFlag = 0 where HotFlag = 1";
            string SQL_UpdateNewsHotFlag2 = @"update NewsInfo set HotFlag = 1 where NewsID in (select top "+topcount+" NewsID from NewsInfo order by ClickCount desc)";
            int isSucess = DB.UpdateRecords(SQL_UpdateNewsHotFlag1);
            isSucess = DB.UpdateRecords(SQL_UpdateNewsHotFlag2);
        }

        /// <summary>
        /// 去掉Category中没有新新闻的标示
        /// </summary>
        public void UpdateNewsCategoryInfoNewFlag()
        {
            DB.DatabaseConnection();
            string SQL_UpdateNewsCategoryNewFlag = @"update NewsCategoryInfo set HasNNews = 0 where NewsCategoryInfo.CategoryID in
                   (select c.CategoryID from (select sum(NewFlag) as FlagCount,CategoryID from NewsInfo group by CategoryID) c 
                   where c.FlagCount = 0)";
            int isSucess = DB.UpdateRecords(SQL_UpdateNewsCategoryNewFlag);
        }

        /// <summary>
        /// 去掉案例中过时的最新标示
        /// </summary>
        /// <param name="spantime"></param>
        public void UpdateWorksInfoNewFlag(int spantime)
        {
            DB.DatabaseConnection();
            string SQL_UpdateWorksNewFlag = @"update WorksInfo set IsNWorks = 0 where DATEDIFF(day,PublishTime,getdate())>" + spantime;
            int isSucess = DB.UpdateRecords(SQL_UpdateWorksNewFlag);
        }

        public void UpdateWorksInfoHotFlag(int topcount)
        {
            DB.DatabaseConnection();
            string SQL_UpdateWorksHotFlag1 = @"update WorksInfo set HotFlag = 0 where HotFlag = 1";
            string SQL_UpdateWorksHotFlag2 = @"update WorksInfo set HotFlag = 1 where WorksID in (select top " + topcount + " WorksID from WorksInfo order by ClickCount desc)";
            int isSucess = DB.UpdateRecords(SQL_UpdateWorksHotFlag1);
            isSucess = DB.UpdateRecords(SQL_UpdateWorksHotFlag2);
        }

        public void UpdateWorksCategoryInfoNewFlag()
        {
            DB.DatabaseConnection();
            string SQL_UpdateWorksCategoryNewFlag = @"update WorksCategoryInfo set HasNWork = 0 where WorksCategoryInfo.CategoryId in
                   (select c.CategoryID from (select sum(IsNWorks) as FlagCount,CategoryID from WorksInfo group by CategoryID) c 
                   where c.FlagCount = 0)";
            int isSucess = DB.UpdateRecords(SQL_UpdateWorksCategoryNewFlag);
        }

        public void ReadtoUpdateParamInfo(SqlDataReader reader,UpdateParamInfo updateparaminfo)
        {
            try
            {
                updateparaminfo.NewsSpanTime = Int32.Parse(reader["NewsSpanTime"].ToString());
                updateparaminfo.NewsUpdateTime = (DateTime)reader["NewsUpdateTime"];
                updateparaminfo.NewsUpdateSpanTime = Int32.Parse(reader["NewsUpdateSpanTime"].ToString());
                updateparaminfo.NewsTop = Int32.Parse(reader["NewsTop"].ToString());
                updateparaminfo.WorksSpanTime = Int32.Parse(reader["WorksSpanTime"].ToString());
                updateparaminfo.WokrsUpdateTime = (DateTime)reader["WokrsUpdateTime"];
                updateparaminfo.WorksUpdateSpanTime = Int32.Parse(reader["WorksUpdateSpanTime"].ToString());
                updateparaminfo.WorksTop = Int32.Parse(reader["WorksTop"].ToString());
            }
            catch
            {
                updateparaminfo.NewsSpanTime = 7;
                updateparaminfo.NewsUpdateTime = System.DateTime.Now;
                updateparaminfo.NewsTop = 5;
                updateparaminfo.NewsUpdateSpanTime = 3;
                updateparaminfo.WorksSpanTime = 7;
                updateparaminfo.WokrsUpdateTime = System.DateTime.Now;
                updateparaminfo.WorksUpdateSpanTime = 3;
                updateparaminfo.WorksTop = 5;
            }
        }

        public void UpdateAllNewsAbout()
        {
            UpdateParamInfo updateparaminfo = GetUpdateParamInfo();
            int isSucess = UpdateTheParamInfo(updateparaminfo);
            DateTime nowtime = System.DateTime.Now;
            TimeSpan span = nowtime - updateparaminfo.NewsUpdateTime;
            int spanhours = span.Days * 24 + span.Hours;
            UpdateNewsInfoNewFlag(updateparaminfo.NewsSpanTime);
            UpdateNewsInfoHotFlag(updateparaminfo.NewsTop);
            UpdateNewsCategoryInfoNewFlag();
            updateparaminfo.NewsUpdateTime = nowtime;
            UpdateTheParamInfo(updateparaminfo);   
        }
        public void UpdateAllWorksAbout()
        {
            UpdateParamInfo updateparaminfo = GetUpdateParamInfo();
            int isSucess = UpdateTheParamInfo(updateparaminfo);
            DateTime nowtime = System.DateTime.Now;
            TimeSpan span = nowtime - updateparaminfo.WokrsUpdateTime;
            int spanhours =span.Days*24 + span.Hours;
            if (spanhours > updateparaminfo.WorksUpdateSpanTime)
            {
                UpdateWorksInfoNewFlag(updateparaminfo.WorksSpanTime);
                UpdateWorksInfoHotFlag(updateparaminfo.WorksTop);
                UpdateWorksCategoryInfoNewFlag();
                updateparaminfo.WokrsUpdateTime = nowtime;
                UpdateTheParamInfo(updateparaminfo);
            }
        }
    }
}
